﻿using Magicodes.ExporterAndImporter.Excel;
using NP.BPMReportPlatform.BPMPlatform;
using NP.BPMReportPlatform.DBHelper;
using NP.BPMReportPlatform.Entities.BPM_Business.HYT;
using NP.BPMReportPlatform.Entities.BPM_Business;
using NP.BPMReportPlatform.HSE;
using NP.BPMReportPlatform.HYT.Dto;
using NP.BPMReportPlatform.Utilities;
using NP.BPMReportPlatform.Utils;
using Serilog;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.Application.Dtos;
using NP.BPMReportPlatform.Reports.YHSQ.Dtos;
using System.Linq;
using Microsoft.Extensions.Configuration;
using MT.Enterprise.Utils.Extensions;
using DocumentFormat.OpenXml.Drawing;

namespace NP.BPMReportPlatform.Reports.YHSQ
{
    public class YHSQService: BPMReportPlatformAppService, IYHSQService
    {
        private readonly SqlSugarClient _db;
        private readonly SqlSugarClient _dbPlatform;
        private readonly IBPMUtilsAppService _bPMUtilsApp;
        private readonly IBPMPlatformAppService _iBPMPlatformApp;
        private readonly SqlSugarClient _dbEngine;
        private readonly ILogger _logger;
        private readonly IExcelImporter _importer;
        private readonly string _pcUrl;
        private readonly IConfiguration _configuration;
        public YHSQService(ISqlSugarAppService sqlSugarAppService, IBPMUtilsAppService bPMUtilsApp
            , IBPMPlatformAppService iBPMPlatformApp, ILogger logger, IExcelImporter importer
            , IConfiguration configuration)
        {
            _db = sqlSugarAppService.GetBusinessInstance();
            _dbPlatform = sqlSugarAppService.GetPlatformInstance();
            _dbEngine = sqlSugarAppService.GetEngineInstance();
            _bPMUtilsApp = bPMUtilsApp;
            _iBPMPlatformApp = iBPMPlatformApp;
            _logger = logger;
            _importer = importer;
            _configuration = configuration;
            _pcUrl = _configuration["BPM:NoAuthPCProcessUrl"];
        }

        public async Task<PagedResultDto<YHSQDto>> GetAllAsync(YHSQInput input)
        {
            RefAsync<int> totalCount = 0;

            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToPageListAsync(input.PageIndex, input.PageSize, totalCount);
            //var userId = await _iBPMPlatformApp.GetUserAsync(CurrentUser.UserName);

            listViewDto.ForEach(x =>
            {
                x.Url = string.Format(_pcUrl, x.InstanceNumber);
                if (x.STime.HasValue && x.STime > DateTime.MinValue &&
                    x.OTime.HasValue && x.OTime > DateTime.MinValue)
                {
                    if (x.OTime < x.STime)
                    {
                        x.DueTime = 0;
                    }
                    else if (x.OTime.Value.Day != x.STime.Value.Day)
                    {
                        // TODO 中午有没有休息时间
                        x.DueTime = x.STime.Value.ToString("yyyy-MM-dd 17:15:00").ToOurDateTime().Subtract(x.STime.Value).TotalMinutes +
                            x.OTime.Value.Subtract(x.OTime.Value.ToString("yyyy-MM-dd 08:30:00").ToOurDateTime()).TotalMinutes +
                            (CalculateBusinessDays(x.STime.Value.AddDays(1), x.OTime.Value.AddDays(-1)) * (8 * 60 + 45));
                        x.DueTime = Math.Round(x.DueTime.Value, 1);
                    }
                    else
                    {
                        x.DueTime = Math.Round(x.OTime.Value.Subtract(x.STime.Value).TotalMinutes, 1);
                    }

                    x.DueTimeHour = Math.Round(x.DueTime.Value / 60, 2);

                    if (!string.IsNullOrEmpty(x.ProcessTopic))
                    {
                        if (x.ProcessTopic.Contains("YH"))
                        {
                            var codes = x.ProcessTopic.Split("YH", StringSplitOptions.RemoveEmptyEntries);
                            if (codes.Length > 1)
                            {
                                x.Code = $"YH{codes[1].Take(13).JoinAsString("")}";
                            }
                        }
                        else if (x.ProcessTopic.Contains("PSA"))
                        {
                            var codes = x.ProcessTopic.Split("PSA", StringSplitOptions.RemoveEmptyEntries);
                            if (codes.Length > 1)
                            {
                                x.Code = $"PSA{codes[1].Take(13).JoinAsString("")}";
                            }
                        }
                    }
                }
            });
            //返回结果
            return new PagedResultDto<YHSQDto>()
            {
                TotalCount = totalCount,
                Items = listViewDto
            };
        }

        private int CalculateBusinessDays(DateTime startDate, DateTime endDate)
        {
            int businessDays = 0;
            DateTime current = startDate;
            while (current <= endDate)
            {
                if (current.DayOfWeek != DayOfWeek.Saturday && current.DayOfWeek != DayOfWeek.Sunday)
                {
                    businessDays++;
                }
                current = current.AddDays(1);
            }
            return businessDays;
        }


        public async Task<List<YHSQDto>> ExportAsync(YHSQInput input)
        {
            var listQueryable = GetQueryable(input);
            var listViewDto = await listQueryable.ToListAsync();
            listViewDto.ForEach(x =>
            {
                if (x.STime.HasValue && x.STime > DateTime.MinValue &&
                    x.OTime.HasValue && x.OTime > DateTime.MinValue)
                {
                    if (x.OTime < x.STime)
                    {
                        x.DueTime = 0;
                    }
                    else if (x.OTime.Value.Day != x.STime.Value.Day)
                    {
                        // TODO 中午有没有休息时间
                        x.DueTime = x.STime.Value.ToString("yyyy-MM-dd 17:15:00").ToOurDateTime().Subtract(x.STime.Value).TotalMinutes +
                            x.OTime.Value.Subtract(x.OTime.Value.ToString("yyyy-MM-dd 08:30:00").ToOurDateTime()).TotalMinutes +
                            (CalculateBusinessDays(x.STime.Value.AddDays(1), x.OTime.Value.AddDays(-1)) * (8 * 60 + 45));
                        x.DueTime = Math.Round(x.DueTime.Value, 1);
                    }
                    else
                    {
                        x.DueTime = Math.Round(x.OTime.Value.Subtract(x.STime.Value).TotalMinutes, 1);
                    }

                    x.DueTimeHour = Math.Round(x.DueTime.Value / 60, 2);

                    if (!string.IsNullOrEmpty(x.ProcessTopic))
                    {
                        if (x.ProcessTopic.Contains("YH"))
                        {
                            var codes = x.ProcessTopic.Split("YH", StringSplitOptions.RemoveEmptyEntries);
                            if (codes.Length > 1)
                            {
                                x.Code = $"YH{codes[1].Take(13).JoinAsString("")}";
                            }
                        }
                        else if (x.ProcessTopic.Contains("PSA"))
                        {
                            var codes = x.ProcessTopic.Split("PSA", StringSplitOptions.RemoveEmptyEntries);
                            if (codes.Length > 1)
                            {
                                x.Code = $"PSA{codes[1].Take(13).JoinAsString("")}";
                            }
                        }
                    }
                }
            });
            return listViewDto;
        }

        /// <summary>
        /// 获取Queryable(报表展现)
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        private ISugarQueryable<YHSQDto> GetQueryable(YHSQInput input)
        {
            var sqlStr = new StringBuilder(@"
SELECT  
    x.InstanceNumber,
    x.InstanceType,
    x.ProcessTopic,
    x.ProcessStatusCode,
    x.ProcessStatusName,
    x.StartUserName,
	x.StartTime,
	x.StartTimeStr,
	x.STime,
	x.STimeStr,
    s.ActivityName,
    s.ResolveUserName,
    s.Summary,
    x.OperateTime,
    x.OperateTimeStr,
	x.OTime,
    x.OTimeStr
FROM BPM_Engine.Records s
JOIN
(
SELECT
    a.InstanceNumber,
    IF(c.BTID='GCEZSLC-YHSQ' || c.BTID='YQSQ-JJSP', '要货申请', '起运量不足申请') AS InstanceType,
    c.ProcessTopic,
    c.ProcessStatusCode,
    c.ProcessStatusName,
    c.StartUserName,
		MIN(a.OperateTime) AS StartTime,
		DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d %H:%i:%s') AS StartTimeStr,
    CAST(CASE
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 1 THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 7 THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 2 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 6 AND MIN(a.OperateTime) > DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 3 DAY), '%Y-%m-%d 08:30:00')
        WHEN MIN(a.OperateTime) < DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 08:30:00') THEN DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 08:30:00')
        WHEN MIN(a.OperateTime) > DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        ELSE MIN(a.OperateTime)
    END AS DATETIME) AS STime,
    DATE_FORMAT(CASE
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 1 THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 7 THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 2 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MIN(a.OperateTime)) = 6 AND MIN(a.OperateTime) > DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 3 DAY), '%Y-%m-%d 08:30:00')
        WHEN MIN(a.OperateTime) < DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 08:30:00') THEN DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 08:30:00')
        WHEN MIN(a.OperateTime) > DATE_FORMAT(MIN(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MIN(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        ELSE MIN(a.OperateTime)
    END, '%Y-%m-%d %H:%i:%s') AS STimeStr,
    a.ActivityName,
    a.ResolveUserName,
    a.Summary,
    MAX(a.OperateTime) AS OperateTime,
    DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d %H:%i:%s') AS OperateTimeStr,
		CAST(CASE
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 1 THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 7 THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 2 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 6 AND MAX(a.OperateTime) > DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 3 DAY), '%Y-%m-%d 08:30:00')
        WHEN MAX(a.OperateTime) < DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 08:30:00') THEN DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 08:30:00')
        WHEN MAX(a.OperateTime) > DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        ELSE MAX(a.OperateTime)
    END AS DATETIME) AS OTime,
    DATE_FORMAT(CASE
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 1 THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 7 THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 2 DAY), '%Y-%m-%d 08:30:00')
        WHEN DAYOFWEEK(MAX(a.OperateTime)) = 6 AND MAX(a.OperateTime) > DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 3 DAY), '%Y-%m-%d 08:30:00')
        WHEN MAX(a.OperateTime) < DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 08:30:00') THEN DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 08:30:00')
        WHEN MAX(a.OperateTime) > DATE_FORMAT(MAX(a.OperateTime), '%Y-%m-%d 17:15:00') THEN DATE_FORMAT(DATE_ADD(MAX(a.OperateTime), INTERVAL 1 DAY), '%Y-%m-%d 08:30:00')
        ELSE MAX(a.OperateTime)
    END, '%Y-%m-%d %H:%i:%s') AS OTimeStr
FROM BPM_Engine.Records a
JOIN BusinessProcessesNew c ON a.InstanceNumber = c.InstanceId 
WHERE (c.BTID = 'GCEZSLC-YHSQ' OR c.BTID = 'ECMR-QYLSPLC' OR c.BTID='YQSQ-JJSP')
GROUP BY a.InstanceNumber
) AS x
ON s.InstanceNumber=x.InstanceNumber AND s.OperateTime=x.OperateTime
");
            var listQueryable = _db.SqlQueryable<YHSQDto>(sqlStr.ToString())
                .WhereIF(!string.IsNullOrEmpty(input.InstanceNumber), x => x.InstanceNumber == input.InstanceNumber)
                .WhereIF(!string.IsNullOrEmpty(input.AddUserName), x => x.StartUserName.Contains(input.AddUserName))
                .WhereIF(!string.IsNullOrEmpty(input.ResolveUserName), x => x.ResolveUserName.Contains(input.ResolveUserName))
                .WhereIF(input.OrderStatus.Any(), x => input.OrderStatus.Contains(x.ProcessStatusCode))
                .WhereIF(input.StartCreationTime != null && input.EndCreationTime != null,
                    x => x.StartTime <= input.EndCreationTime && x.StartTime >= input.StartCreationTime)
                .WhereIF(input.StartOperateTime != null && input.EndOperateTime != null,
                    x => x.OperateTime <= input.EndCreationTime && x.OperateTime >= input.StartCreationTime)
                .OrderBy(x => x.StartTime, OrderByType.Desc);
            return listQueryable;
        }
    }
}
